{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Tce3stUlHN0L"
      },
      "source": [
        "##### Copyright 2020 The TensorFlow IO Authors."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "cellView": "form",
        "id": "tuOe1ymfHZPu"
      },
      "outputs": [],
      "source": [
        "#@title Licensed under the Apache License, Version 2.0 (the \"License\");\n",
        "# you may not use this file except in compliance with the License.\n",
        "# You may obtain a copy of the License at\n",
        "#\n",
        "# https://www.apache.org/licenses/LICENSE-2.0\n",
        "#\n",
        "# Unless required by applicable law or agreed to in writing, software\n",
        "# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
        "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
        "# See the License for the specific language governing permissions and\n",
        "# limitations under the License."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qFdPvlXBOdUN"
      },
      "source": [
        "# TensorFlow IO から PostgreSQL データベースを読み取る"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MfBg1C5NB3X0"
      },
      "source": [
        "<table class=\"tfo-notebook-buttons\" align=\"left\">\n",
        "  <td><a target=\"_blank\" href=\"https://www.tensorflow.org/io/tutorials/postgresql\"><img src=\"https://www.tensorflow.org/images/tf_logo_32px.png\"> TensorFlow.orgで表示</a></td>\n",
        "  <td><a target=\"_blank\" href=\"https://colab.research.google.com/github/tensorflow/docs-l10n/blob/master/site/ja/io/tutorials/postgresql.ipynb\"><img src=\"https://www.tensorflow.org/images/colab_logo_32px.png\"> Google Colab で実行</a></td>\n",
        "  <td><a target=\"_blank\" href=\"https://github.com/tensorflow/docs-l10n/blob/master/site/ja/io/tutorials/postgresql.ipynb\"><img src=\"https://www.tensorflow.org/images/GitHub-Mark-32px.png\">GitHub でソースを表示{</a></td>\n",
        "      <td><a href=\"https://storage.googleapis.com/tensorflow_docs/docs-l10n/site/ja/io/tutorials/postgresql.ipynb\"><img src=\"https://www.tensorflow.org/images/download_logo_32px.png\">ノートブックをダウンロード/a0}</a></td>\n",
        "</table>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xHxb-dlhMIzW"
      },
      "source": [
        "## 概要\n",
        "\n",
        "このチュートリアルでは、トレーニングまたは推論のために PostgreSQL データベースサーバーから`tf.data.Dataset`を作成し、作成した`Dataset`を`tf.keras`に渡す方法を紹介します。\n",
        "\n",
        "SQL データベースは、データサイエンティストにとって重要なデータソースです。最も人気のあるオープンソース SQL データベースの 1 つである [PostgreSQL](https://www.postgresql.org) は、企業全体の重要なデータやトランザクションデータを格納するために広く使用されています。PostgreSQL データベースサーバーから直接`Dataset`を作成し、トレーニングまたは推論のために`Dataset`を`tf.keras`に渡すと、データパイプラインを大幅に簡略化されるのでデータサイエンティストは機械学習モデルの構築に専念できます。"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MUXex9ctTuDB"
      },
      "source": [
        "## セットアップと使用法"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "upgCc3gXybsA"
      },
      "source": [
        "### 必要な tensorflow-io パッケージをインストールし、ランタイムを再起動する"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "uUDYyMZRfkX4"
      },
      "outputs": [],
      "source": [
        "try:\n",
        "  %tensorflow_version 2.x\n",
        "except Exception:\n",
        "  pass\n",
        "\n",
        "!pip install tensorflow-io"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yZmI7l_GykcW"
      },
      "source": [
        "### PostgreSQL のインストールとセットアップ (オプション)\n",
        "\n",
        "**注: このノートブックは、Google Colab でのみ実行するように設計されています**。*システムにパッケージをインストールし、sudo アクセスが必要です。ローカルの Jupyter ノートブックで実行する場合は、注意して続行してください。*\n",
        "\n",
        "Google Colab での使用法をデモするには、PostgreSQL サーバーをインストールします。パスワードと空のデータベースも必要です。\n",
        "\n",
        "このノートブックを Google Colab で実行していない場合、または既存のデータベースを使用する場合は、次の設定をスキップして次のセクションに進んでください。"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "YUj0878jPyz7"
      },
      "outputs": [],
      "source": [
        "# Install postgresql server\n",
        "!sudo apt-get -y -qq update\n",
        "!sudo apt-get -y -qq install postgresql\n",
        "!sudo service postgresql start\n",
        "\n",
        "# Setup a password `postgres` for username `postgres`\n",
        "!sudo -u postgres psql -U postgres -c \"ALTER USER postgres PASSWORD 'postgres';\"\n",
        "\n",
        "# Setup a database with name `tfio_demo` to be used\n",
        "!sudo -u postgres psql -U postgres -c 'DROP DATABASE IF EXISTS tfio_demo;'\n",
        "!sudo -u postgres psql -U postgres -c 'CREATE DATABASE tfio_demo;'"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e4_59-RLMRgT"
      },
      "source": [
        "### 必要な環境変数を設定する\n",
        "\n",
        "次の環境変数は、前のセクションの PostgreSQL 設定に基づいています。設定が異なる場合、または既存のデータベースを使用している場合は、それに応じて変更する必要があります。\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0dRotqDMswcK"
      },
      "outputs": [],
      "source": [
        "%env TFIO_DEMO_DATABASE_NAME=tfio_demo\n",
        "%env TFIO_DEMO_DATABASE_HOST=localhost\n",
        "%env TFIO_DEMO_DATABASE_PORT=5432\n",
        "%env TFIO_DEMO_DATABASE_USER=postgres\n",
        "%env TFIO_DEMO_DATABASE_PASS=postgres"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9reCVv0mE_9O"
      },
      "source": [
        "### PostgreSQL サーバーでデータを準備する\n",
        "\n",
        "このチュートリアルではデータベースを作成し、デモのためにデータベースにデータを入力します。このチュートリアルで使用されるデータは、[Air Quality Data Set](https://archive.ics.uci.edu/ml/datasets/Air+Quality) からのデータで、[UCI Machine Learning Repository](http://archive.ics.uci.edu/ml) から入手できます。\n",
        "\n",
        "以下は、Air Quality Data Set のサブセットのプレビューです。\n",
        "\n",
        "Date | Time | CO(GT) | PT08.S1(CO) | NMHC(GT) | C6H6(GT) | PT08.S2(NMHC) | NOx(GT) | PT08.S3(NOx) | NO2(GT) | PT08.S4(NO2) | PT08.S5(O3) | T | RH | AH\n",
        "--- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | ---\n",
        "10/03/2004 | 18.00.00 | 2,6 | 1360 | 150 | 11,9 | 1046 | 166 | 1056 | 113 | 1692 | 1268 | 13,6 | 48,9 | 0,7578\n",
        "10/03/2004 | 19.00.00 | 2 | 1292 | 112 | 9,4 | 955 | 103 | 1174 | 92 | 1559 | 972 | 13,3 | 47,7 | 0,7255\n",
        "10/03/2004 | 20.00.00 | 2,2 | 1402 | 88 | 9,0 | 939 | 131 | 1140 | 114 | 1555 | 1074 | 11,9 | 54,0 | 0,7502\n",
        "10/03/2004 | 21.00.00 | 2,2 | 1376 | 80 | 9,2 | 948 | 172 | 1092 | 122 | 1584 | 1203 | 11,0 | 60,0 | 0,7867\n",
        "10/03/2004 | 22.00.00 | 1,6 | 1272 | 51 | 6,5 | 836 | 131 | 1205 | 116 | 1490 | 1110 | 11,2 | 59,6 | 0,7888\n",
        "\n",
        "大気質データセットと UCI 機械学習リポジトリの詳細については、[参照文献](#references)セクションをご覧ください。\n",
        "\n",
        "データの準備をシンプルにするために、Air Quality Data Setの SQL バージョンが用意されており、[AirQualityUCI.sql](https://github.com/tensorflow/io/blob/master/docs/tutorials/postgresql/AirQualityUCI.sql) として入手できます。\n",
        "\n",
        "表を作成するステートメントは次のとおりです。\n",
        "\n",
        "```\n",
        "CREATE TABLE AirQualityUCI (   Date DATE,   Time TIME,   CO REAL,   PT08S1 INT,   NMHC REAL,   C6H6 REAL,   PT08S2 INT,   NOx REAL,   PT08S3 INT,   NO2 REAL,   PT08S4 INT,   PT08S5 INT,   T REAL,   RH REAL,   AH REAL );\n",
        "```\n",
        "\n",
        "データベースに表を作成してデータを入力するための完全なコマンドは以下のとおりです。"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "W1eVidg3JrPV"
      },
      "outputs": [],
      "source": [
        "!curl -s -OL https://github.com/tensorflow/io/raw/master/docs/tutorials/postgresql/AirQualityUCI.sql\n",
        "\n",
        "!PGPASSWORD=$TFIO_DEMO_DATABASE_PASS psql -q -h $TFIO_DEMO_DATABASE_HOST -p $TFIO_DEMO_DATABASE_PORT -U $TFIO_DEMO_DATABASE_USER -d $TFIO_DEMO_DATABASE_NAME -f AirQualityUCI.sql"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "acEST3amdyDI"
      },
      "source": [
        "### PostgreSQL サーバーからデータセットを作成し、TensorFlow で使用する\n",
        "\n",
        "PostgreSQL サーバーからのデータセットの作成は、`query`および`endpoint`引数を指定して`tfio.experimental.IODataset.from_sql`を呼び出して簡単に実行できます。`query`はテーブル内の選択した列の SQL クエリで、`endpoint`引数はアドレスとデータベース名です。"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "h21RdP7meGzP"
      },
      "outputs": [],
      "source": [
        "import os\n",
        "import tensorflow_io as tfio\n",
        "\n",
        "endpoint=\"postgresql://{}:{}@{}?port={}&dbname={}\".format(\n",
        "    os.environ['TFIO_DEMO_DATABASE_USER'],\n",
        "    os.environ['TFIO_DEMO_DATABASE_PASS'],\n",
        "    os.environ['TFIO_DEMO_DATABASE_HOST'],\n",
        "    os.environ['TFIO_DEMO_DATABASE_PORT'],\n",
        "    os.environ['TFIO_DEMO_DATABASE_NAME'],\n",
        ")\n",
        "\n",
        "dataset = tfio.experimental.IODataset.from_sql(\n",
        "    query=\"SELECT co, pt08s1 FROM AirQualityUCI;\",\n",
        "    endpoint=endpoint)\n",
        "\n",
        "print(dataset.element_spec)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8y-VpwcWNYTF"
      },
      "source": [
        "上記の`dataset.element_spec`の出力からわかるように、作成された`Dataset`の要素は、データベーステーブルの列名をキーとして持つ python dict オブジェクトです。\n",
        "\n",
        "```\n",
        "{   'co': TensorSpec(shape=(), dtype=tf.float32, name=None),   'pt08s1': TensorSpec(shape=(), dtype=tf.int32, name=None), }\n",
        "```\n",
        "\n",
        "さらに簡単に演算を実行できます。たとえば、`Dataset`の`nox`フィールドと`no2`フィールドの両方を選択して、差分を計算できます"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qCoueXYZOvqZ"
      },
      "outputs": [],
      "source": [
        "dataset = tfio.experimental.IODataset.from_sql(\n",
        "    query=\"SELECT nox, no2 FROM AirQualityUCI;\",\n",
        "    endpoint=endpoint)\n",
        "\n",
        "dataset = dataset.map(lambda e: (e['nox'] - e['no2']))\n",
        "\n",
        "# check only the first 20 record\n",
        "dataset = dataset.take(20)\n",
        "\n",
        "print(\"NOx - NO2:\")\n",
        "for difference in dataset:\n",
        "  print(difference.numpy())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xO2pheWEPQSU"
      },
      "source": [
        "作成された`Dataset`は、トレーニングまたは推論のために直接`tf.keras`に渡す準備ができています。"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AzQoxSlHVv1k"
      },
      "source": [
        "## 参照文献\n",
        "\n",
        "- Dua, D. and Graff, C. (2019). UCI Machine Learning Repository [http://archive.ics.uci.edu/ml]. Irvine, CA: University of California, School of Information and Computer Science.\n",
        "- S. De Vito, E. Massera, M. Piga, L. Martinotto, G. Di Francia, On field calibration of an electronic nose for benzene estimation in an urban pollution monitoring scenario, Sensors and Actuators B: Chemical, Volume 129, Issue 2, 22 February 2008, Pages 750-757, ISSN 0925-4005"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [
        "Tce3stUlHN0L"
      ],
      "name": "postgresql.ipynb",
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}
